--------------------------------------------------------------------------------
-- @name: ora 1555
-- @author: dion cho
-- @note: test case for ora-01555 error
--------------------------------------------------------------------------------

set echo on
set pages 10000
set lines 200
set timing off
set trimspool on
set serveroutput off


----------------------------------------------------------------------------
-- Case 1: undo block overwritten
-- create small undo tablespace
drop tablespace small_undo including contents and datafiles;

create undo tablespace small_undo
datafile size 10m autoextend off
;

-- set undo tablespace
alter system set undo_tablespace = small_undo scope=memory;


drop table t1 purge;

create table t1(c1 int, c2 char(2000), c3 char(2000), c4 char(2000));

insert into t1 values(1, 'x', 'x', 'x');
commit;


-- snapshot too old error
-- session #2
select * from t1 where tpack.f_wait_for_signal = 1;

-- session #1
begin 
  for idx in 1 .. 3000 loop
    update t1 set c2 = idx, c3 = idx, c4 = idx where c1 = 1;
    if mod(idx, 1000) = 0 then
      commit;
    end if;
  end loop;
end;
/

commit;

exec tpack.signal;

-- session #2
select * from t1 where tpack.f_wait_for_signal = 1;

-- session #1
exec tpack.signal;



-- SQL*Trace
-- session #2
exec tpack.begin_diag_trace(userenv('sid'), 10046, 1);

select * from t1 where tpack.f_wait_for_signal = 1;

exec tpack.end_diag_trace(userenv('sid'), 10046);

select * from table(tpack.get_diag_trace(userenv('sid'), 'TKPROF'));

-- session #1
begin 
  for idx in 1 .. 3000 loop
    update t1 set c2 = idx, c3 = idx, c4 = idx where c1 = 1;
    if mod(idx, 1000) = 0 then
      commit;
    end if;
  end loop;
end;
/

commit;

exec tpack.signal;


-- session snapshot report
-- session #2
exec tpack.begin_session_snapshot;

select * from t1 where tpack.f_wait_for_signal = 1;

exec tpack.add_session_snapshot;

col item format a50
select type, item, total_delta from table(tpack.session_snapshot_report);


-- session #1
begin 
  for idx in 1 .. 3000 loop
    update t1 set c2 = idx, c3 = idx, c4 = idx where c1 = 1;
    if mod(idx, 1000) = 0 then
      commit;
    end if;
  end loop;
end;
/

commit;

exec tpack.signal;


-- undostat
set serveroutput on
exec print_table('select * from v$undostat where ssolderrcnt > 0 order by end_time desc');
set serveroutput off

alter system set undo_tablespace = undotbs1;


------------------------------------------------------------------------------------
-- Case 2: transaction table overwritten

-- create small undo tablespace
drop tablespace small_undo including contents and datafiles;

create undo tablespace small_undo
datafile size 10m autoextend off
;

-- set undo tablespace
alter system set undo_tablespace = small_undo scope=memory;

-- make buffer cache small (for delayed block cleanout)
alter system set sga_target=0 scope=memory;
alter system set db_cache_size=1m scope=memory;

show sga

drop table t1 purge;

create table t1(c1 int, c2 char(100));

create index t1_n1 on t1(c1);

drop table t2 purge;

create table t2(c1 int);

-- generate data
insert into t1
select level, 'x'
from dual
connect by level <= 35000
;

commit;

insert into t2 values(1);

commit;

@gather t1
@gather t2


-- session monitoring
-- session #1
update t1 set c2 = 'y';

commit;

-- session #2
set arraysize 1

exec tpack.begin_session_snapshot;

exec tpack.begin_diag_trace(userenv('sid'), 10046, 1);

select /*+ index(t1) */ * from t1 where tpack.sleep(c1, 1) = 1 and c1 > 0;

exec tpack.end_diag_trace(userenv('sid'), 10046);

exec tpack.add_session_snapshot;

select * from table(tpack.get_diag_trace(userenv('sid'), 'TKPROF'));

col item format a50
select type, item, total_delta from table(tpack.session_snapshot_report);


-- session #1
begin 
  for idx in 1 .. 1000000 loop
    update t2 set c1 = c1 + 1;
    commit;
  end loop;
end;
/

-- undostat
set serveroutput on
exec print_table('select * from v$undostat where ssolderrcnt > 0 order by end_time desc');


alter system set db_cache_size = 50m;

alter system set undo_tablespace = undotbs1;

